from dbutils.pooled_db import PooledDB
import pymysql
from flaskDemo.extensions.logger import MyLogger
logger = MyLogger().getLogger()
class mysql_db(object):
    def __init__(self):
        self.pool = PooledDB(
            creator=pymysql,
            maxconnections=6,
            mincached=2,
            maxcached=5,
            blocking=True,
            host='localhost',
            user='root',
            passwd='root',
            db='naive',
            port=3306,
            charset='utf8'
        )

    def fetch_all(self,sql,*args):
        self.cursor.execute(sql,args)
        logger.info(sql)
        result = self.cursor.fetchall()
        return result

    def fetch_one(self,sql,*args):
        self.cursor.execute(sql,args)
        logger.info(sql)
        result = self.cursor.fetchone()
        return result

    def update(self,sql,*args):
        self.cursor.execute(sql, args)
        logger.info(sql)
        return self.cursor.rowcount

    def insert(self,sql,table_name=0,id=False):
        self.cursor.execute(sql)
        logger.info(sql)
        if id and table_name != 0:
            self.cursor.execute("SELECT @INSERT_ID")
            result = self.cursor.fetchone()
            if not result[0]:
                self.cursor.execute(f"""
                CREATE TRIGGER {table_name}_insert
                BEFORE INSERT ON {table_name}
                FOR EACH ROW
                BEGIN
                  SET @INSERT_ID = NEW.id;
                END;
                """)
                self.cursor.commit()
                self.cursor.execute("SELECT @INSERT_ID")
                result = self.cursor.fetchone()
                return result[0]
            else:
                return result[0]
        return 0

    def delete(self,sql,*args):
        self.cursor.execute(sql, args)
        logger.info(sql)
        return self.cursor.rowcount

    def __enter__(self):
        self.conn = self.pool.connection()
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.conn.close()

def task1():
    # print(f"{mysql_db()} 对象")
    # sql = "insert into system_role (role_name,details) values ('ls','ls')"
    # sql = "select * from system_role where id = %s"
    sql = "update system_role set role_name = %s where details = %s"
    with mysql_db() as db:
        result = db.update(sql,'admin','ls')
        db.conn.commit()
        print(result)
if __name__ == "__main__":
    task1()

